package com.study.dao.impl;

import com.study.bean.entity.DormitoryBuilding;
import com.study.bean.entity.StuCounter;
import com.study.bean.entity.Student;
import com.study.bean.query.BuildAdmin;
import com.study.bean.query.Rooms;
import com.study.bean.query.StudentQuery;
import com.study.bean.vo.StudentVO;
import com.study.dao.StudentDao;
import com.study.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public List<StudentVO> selectByPage(StudentQuery studentQuery) {
        String sql = "SELECT  s.*,a.`m_name`  FROM `students` s , `dormitory_admins` a WHERE s.admin_id = a.admin_id";
        List<Object> list = new ArrayList<>();
        System.out.println(studentQuery);
        if ( studentQuery.getName() != null && !studentQuery.getName().isEmpty()){
            sql += " and s.name like ?";
            list.add("%"+studentQuery.getName()+"%");
        }
        if (studentQuery.getAge() != null){
            sql += " and s.age = ?";
            list.add(studentQuery.getAge() );
        }
        if ( studentQuery.getSex() != null && !studentQuery.getSex().isEmpty()  ){
            sql += " and s.sex = ?";
            list.add(studentQuery.getSex());
        }
        if (studentQuery.getMajor() != null && !studentQuery.getMajor().isEmpty()  ){
            sql += " and s.major like ?";
            list.add("%"+studentQuery.getMajor()+"%");
        }
        if ( studentQuery.getClassName() != null && !studentQuery.getClassName().isEmpty()  ){
            sql += " and s.class_name like ?";
            list.add("%"+studentQuery.getClassName()+"%");
        }
        if ( studentQuery.getDormitoryId() != null){
            sql += " and s.dormitory_id = ?";
            list.add(studentQuery.getDormitoryId());
        }
        if ( studentQuery.getBuildingId() != null){
            sql += " and s.building_id = ?";
            list.add(studentQuery.getBuildingId());
        }
        if (studentQuery.getAdminId() != null){
            sql += " and s.admin_id = ?";
            list.add(studentQuery.getAdminId());
        }
        //构造一个集合存放所有的条件
        sql += " limit ?,?";
        int offset = (studentQuery.getPage() - 1) * studentQuery.getLimit();
        list.add(offset);
        list.add(studentQuery.getLimit());
        List<StudentVO> list1 = template.query(sql,
                new BeanPropertyRowMapper<>(StudentVO.class), list.toArray());
        list1.forEach(System.out::println);
        return list1;
    }

    @Override
    public int selectTotalCount(StudentQuery studentQuery) {
        String sql = "select count(*) from students where 1=1";
        //构造一个集合存放所有的条件
        List<Object> list = new ArrayList<>();
        if ( studentQuery.getName() != null && !studentQuery.getName().isEmpty()){
            sql += " and name like ?";
            list.add("%"+studentQuery.getName()+"%");
        }
        if (studentQuery.getAge() != null){
            sql += " and age = ?";
            list.add(studentQuery.getAge() );
        }
        if ( studentQuery.getSex() != null && !studentQuery.getSex().isEmpty()  ){
            sql += " and sex = ?";
            list.add(studentQuery.getSex());
        }
        if (studentQuery.getMajor() != null && !studentQuery.getMajor().isEmpty()  ){
            sql += " and major like ?";
            list.add("%"+studentQuery.getMajor()+"%");
        }
        if ( studentQuery.getClassName() != null && !studentQuery.getClassName().isEmpty()  ){
            sql += " and class_name like ?";
            list.add("%"+studentQuery.getClassName()+"%");
        }
        if ( studentQuery.getDormitoryId() != null){
            sql += " and dormitory_id = ?";
            list.add(studentQuery.getDormitoryId());
        }
        if ( studentQuery.getBuildingId() != null){
            sql += " and building_id = ?";
            list.add(studentQuery.getBuildingId());
        }
        if (studentQuery.getAdminId() != null){
            sql += " and admin_id = ?";
            list.add(studentQuery.getAdminId());
        }


        Integer i = template.queryForObject(sql, Integer.class,list.toArray());
        return i;
    }


    @Override
    public int deleteById(int id, int status) {
        String updatesql = "update `students` set status = ? where student_id = ?";
        int update = template.update(updatesql, status,id);
        String select = "select * from `students` where student_id=?";
        Student student = template.queryForObject(select, new BeanPropertyRowMapper<>(Student.class), id);
        String cause =null;
        if (status ==0){
            cause = "入住";
        }else if(status == 1){
            cause = "即将入驻";
        }else if (status == 3){
            cause = "退住";
        }
        Date  date = new Date();
        //获取当前时间
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String insert  = "INSERT INTO `record` (`stu_id`, `build_id`, `dorm_id`, `start`, `end`, `cause`,`mtime`) VALUES (?, ?, ?, ?, ?, ?,?);";
        int update1 = template.update(insert, student.getStudentId(), student.getBuildingId(), student.getDormitoryId(), student.getStart(), student.getEnd(), cause,formatter.format(date));
        return update;
    }
    @Override
    public void add(Student Student) {

    }

    @Override
    public StudentVO selectById(int id) {

        String sql = "\n" +
                "SELECT s.*,a.`m_name` FROM`students` s,`dormitory_admins` a WHERE s.admin_id = a.admin_id and s.student_id = ?";
        StudentVO studentVO = template.queryForObject(sql, new BeanPropertyRowMapper<>(StudentVO.class), id);
        return studentVO;

    }

    @Override
    public void update(Student Student) {

    }



    @Override
    public List<DormitoryBuilding> selectbuild() {
        String sql = "select * from dormitory_buildings";

        List<DormitoryBuilding> query =
                template.query(sql, new BeanPropertyRowMapper<>(DormitoryBuilding.class));
        return query;
    }

    @Override
    public List<Rooms> selectRoom(int buildId) {
        String sql = "select `dormitory_id` from `dormitories`  where building_id = ?";
        List<Rooms> query = template.query(sql, new BeanPropertyRowMapper<>(Rooms.class), buildId);
        return query;
    }

    @Override
    public List<BuildAdmin> selectAdmin(int buildId) {
        String sql = "SELECT db.building_id,da.admin_id,da.m_name\n" +
                "FROM dormitory_buildings db,dormitory_admins da\n" +
                "WHERE db.administrator_id = da.admin_id and building_id = ?";

        List<BuildAdmin> query = template.query(sql, new BeanPropertyRowMapper<>(BuildAdmin.class), buildId);
        return query;
    }

    @Override
    public Integer addStudent(Student student) {
        String sql = "INSERT INTO students (name, sex, age, major, class_name, dormitory_id, building_id, admin_id, start, end)\n" +
                "VALUES (?, ?, ?,? , ?,? ,? , ?);\n";

        int update = template.update(sql, student.getName(), student.getSex(), student.getAge(),
                student.getMajor(), student.getClassName(), student.getDormitoryId(), student.getBuildingId(), student.getAdminId(),student.getStart(),student.getEnd());

        int studentId = template.queryForObject("select LAST_INSERT_ID()", Integer.class);
        String add = "INSERT INTO `record` (`stu_id`, `build_id`, `dorm_id`, `start`, `end`, `cause`) VALUES (?, ?, ?, ?, ?, ?);";
        int update1 = template.update(add, studentId, student.getBuildingId(), student.getDormitoryId(), student.getStart(), student.getEnd(), "入住");
        return update;
    }

    @Override
    public Integer updateStudent(Student student) {
        String sql = "UPDATE students\n" +
                "SET name = ?,\n" +
                "    sex =?,\n" +
                "    age = ?,\n" +
                "    major = ?,\n" +
                "    class_name = ?,\n" +
                "    dormitory_id = ?,\n" +
                "    building_id = ?,\n" +
                "    admin_id = ?,\n" +
                "start = ?,\n"+
                "end = ?\n"+
                "WHERE student_id = ?;\n";
        int update = template.update(sql, student.getName(), student.getSex(), student.getAge(),
                student.getMajor(), student.getClassName(), student.getDormitoryId(), student.getBuildingId(), student.getAdminId(),student.getStart(),student.getEnd(),student.getStudentId());
        String record = "INSERT INTO `record` (`stu_id`, `build_id`, `dorm_id`, `start`, `end`, `cause`)  VALUES (?, ?, ?, ?, ?, ?);";
        int update1 = template.update(record, student.getStudentId(), student.getBuildingId(), student.getDormitoryId(), student.getStart(), student.getEnd(), "更新住宿信息");

        return update;
    }

    @Override
    public StuCounter login(String sno, String password) {
        String sql = "select * from user where sno = ? and password = ?";
        StuCounter stuCounter = template.queryForObject(sql, new BeanPropertyRowMapper<>(StuCounter.class), sno, password);
        return stuCounter;
    }
}
